package com.hejia.alauda.persistence.mybatis;

import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.hejia.alauda.entity.DayCount;
import com.hejia.alauda.entity.UserOrderDayStats;
import com.hejia.alauda.persistence.BaseMapper;

public interface UserOrderDayStatsMapper extends BaseMapper<UserOrderDayStats> {

	/**
	 * 按天统计注册用户
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	@Select("select referee 'sourceId', date(createDate) 'date', count(id) 'count' from user"
			+ " where createDate >= #{startDate} and createDate <= #{endDate}"
			+ " and referee in (select sourceId from popularize union select number as 'sourceId' from channel)"
			+ " group by referee, date(createDate)")
	List<DayCount> statsDayRegisteredUser(
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 按天统计新增交易用户和交易金额
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	@Select("select u.referee 'sourceId', date(ufo.createDate) 'date', count(ufo.user_id) 'count', sum(ufo.amount) 'amount' from"
			+ "	(select * from ("
			+ "			select * from `order` where status = 'STATUS_PAID' order by createDate asc) oo"
			+ "		group by oo.user_id) ufo join user u on ufo.user_id = u.id"
			+ "	where ufo.createDate >= #{startDate}"
			+ "	and ufo.createDate <= #{endDate}"
			+ " and u.referee in (select sourceId from popularize union select number as 'sourceId' from channel)"
			+ " group by u.referee, date(ufo.createDate)")
	List<DayCount> statsDayNewTradeUserAndAmount(
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 按天统计交易订单数
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	@Select("select u.referee 'sourceId', date(o.createDate) 'date', count(o.id) 'count', sum(o.amount) 'amount'"
			+ " from `order` o join user u on o.user_id = u.id"
			+ " where o.createDate >= #{startDate} and o.createDate <= #{endDate}"
			+ " and u.referee in (select sourceId from popularize union select number as 'sourceId' from channel)"
			+ " group by u.referee, date(o.createDate)")
	List<DayCount> statsDayOrder(
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 按天统计成功交易订单数和金额
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	@Select("select u.referee 'sourceId', date(o.createDate) 'date', count(o.id) 'count', sum(o.amount) 'amount'"
			+ " from `order` o join user u on o.user_id = u.id"
			+ " where o.createDate >= #{startDate} and o.createDate <= #{endDate}"
			+ " and o.status = 'STATUS_PAID'"
			+ " and u.referee in (select sourceId from popularize union select number as 'sourceId' from channel)"
			+ " group by u.referee, date(o.createDate)")
	List<DayCount> statsDaySuccessOrderAndAmount(
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 按天统计复投订单数和金额
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	@Select("select u.referee 'sourceId', date(o.createDate) 'date', count(o.id) 'count', sum(o.amount) 'amount'"
			+ " from `order` o join user u on o.user_id = u.id"
			+ " where o.createDate >= #{startDate} and o.createDate <= #{endDate}"
			+ " and o.status = 'STATUS_PAID'"
			+ " and u.referee in (select sourceId from popularize union select number as 'sourceId' from channel)"
			+ " and o.id not in (select ufo.id from (select * from (select * from `order` where status = 'STATUS_PAID' order by createDate asc) oo group by oo.user_id) ufo)"
			+ " group by u.referee, date(o.createDate)")
	List<DayCount> statsDayRepeatOrderAndAmount(
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 计算用户订单数
	 * @param biggerCategoryIds	推广大类ID集合
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	List<UserOrderDayStats> sumUserOrderByBiggerCatogoryIds(
			@Param("biggerCategoryIds") List<Long> biggerCategoryIds, 
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 计算用户订单数
	 * @param biggerCategoryIds	推广大类ID
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	List<UserOrderDayStats> sumUserOrderByBiggerCatogoryId(
			@Param("biggerCategoryId") Long biggerCategoryId, 
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);

	/**
	 * 计算用户订单数
	 * @param biggerCategoryIds	推广类别ID
	 * @param startDate	开始日期
	 * @param endDate	结束日期
	 * @return
	 */
	List<UserOrderDayStats> sumUserOrderByCatogoryId(
			@Param("categoryId") Long categoryId,
			@Param("startDate") Date startDate,
			@Param("endDate") Date endDate);
	
}
